<?php

/**
 * @author 
 * @copyright 2012
 */
class Datphong_model extends CI_Model {

    function kiemtradatphong() {
        $loaiphong = $this->input->post('loaiphong');
        $ngaybatdau = $this->input->post('ngaybatdau');
        $thangbatdau = $this->input->post('thangbatdau');
        $nambatdau = $this->input->post('nambatdau');

        $ngayketthuc = $this->input->post('ngayketthuc');
        $thangketthuc = $this->input->post('thangketthuc');
        $namketthuc = $this->input->post('namketthuc');

        $ts = mktime(0, 0, 0, $thangbatdau, $ngaybatdau, $nambatdau);
        $startdate = date("Y-m-d", $ts);
        $startdate = date_create($startdate);

        $ts = mktime(0, 0, 0, $thangketthuc, $ngayketthuc, $namketthuc);
        $enddate = date("Y-m-d", $ts);
        $enddate = date_create($enddate);

        date_add($enddate, date_interval_create_from_date_string('1 days'));
        do {
            $start = date_format($startdate, 'Y-m-d');
            date_add($startdate, date_interval_create_from_date_string('1 days'));
            $strSQL = "SELECT max(SoLuongPhongTrong) as max
						FROM PhongTrongTheoNgay pt
						WHERE pt.Ngay = ? AND pt.LoaiPhong = ?";
            $query = $this->db->query($strSQL, array($start, $loaiphong));

            if ($query->num_rows() > 0) {
                foreach ($query->result() as $row) {
                    $SoLuongPhongTrong = $row->max;
                }
            }
            if ($SoLuongPhongTrong == 0)
                return 0;
            //else $SoLuongPhongTrong = 0;
        } while ($startdate != $enddate);
        return 1;
    }
	
	function ThongTinDatPhong($MaPhieuDat) {
		$sql = 'select MaPD, MaKS, KhachSan.Ten as TenKhachSan, LoaiPhong.Ten as TenLoaiPhong, SoNha, Duong, Quan, Tinh_ThanhPho, QuocGia, 
		KhachSan.DienThoai as DienThoai, KhachSan.Email as Email, PhiDichVu, VAT from KhachSan, LoaiPhong, PhieuDatPhong
		where KhachSan.MaKS = LoaiPhong.KhachSan and PhieuDatPhong.LoaiPhong = LoaiPhong.MaLP and MaPD = ?';
		
		$q = $this->db->query($sql, array($MaPhieuDat));
        if ($q->num_rows() > 0) {
            foreach ($q->result() as $row) {
                $khachsan = $row;
            }
        }
		
		$sql = 'select Email, DienThoai, NgayDatPhong, GioNhanPhong, PhanTramGiam, TongTien, TinhTrangThanhToan, SoThe 
		from PhieuDatPhong where MaPD = ?';	
		
		$q = $this->db->query($sql, array($MaPhieuDat));
        if ($q->num_rows() > 0) {
            foreach ($q->result() as $row) {
                $PhieuDatPhong = $row;
            }
        }
		
		$sql = 'select HoTenNguoiDaiDien, SoNguoiLon, SoTreEm, SoTienThemNguoi, LoaiGiuong.Ten as TenGiuong 
				from ChiTietPhieuDat, LoaiGiuong where ChiTietPhieuDat.LoaiGiuong = LoaiGiuong.MaLG and PhieuDatPhong = ?';	
		
		$q = $this->db->query($sql, array($MaPhieuDat));
        if ($q->num_rows() > 0) {
            foreach ($q->result() as $row) {
                $ChiTietPheuDat[] = $row;
            }
        }
		$sql = 'select Ngay, Gia from GiaThueTheoNgay where PhieuDatPhong = ?';
		$q = $this->db->query($sql, array($MaPhieuDat));
		$i = 0;
        if ($q->num_rows() > 0) {
            foreach ($q->result() as $row) {				
                $GiaThueTheoNgay[] = $row;
				$i++;
            }
        }
		$data['KhachSan'] = $khachsan;
		$data['PhieuDatPhong'] = $PhieuDatPhong;
		$data['ChiTietPheuDat'] = $ChiTietPheuDat;
		$data['GiaThueTheoNgay'] = $GiaThueTheoNgay;
		$data['SoNgay'] = $i;
		return $data;	
    }


    function laygia_index($makhachsan) {
        $sql = 'select max(SoLuongPhongTrong) as max, GiaSauKhuyenMai as gia from PhongTrongTheoNgay where LoaiPhong = ? and Ngay=?';
        $this->db->select('MaLP');
        $this->db->from('LoaiPhong');
        $this->db->where('KhachSan', $makhachsan);
        $q = $this->db->get();
        if ($q->num_rows() > 0) {
            foreach ($q->result() as $row) {
                $ngay = date('j');
                $thang = date('n');
                $nam = date('Y');
                $songay = date('t');
                for ($i = 0; $i < 14; $i++) {
                    $d = $ngay + $i;
                    if ($d < $songay + 1) {
                        $ts = mktime(0, 0, 0, $thang, $d, $nam);
                        $q_1 = $this->db->query($sql, array($row->MaLP, date("Y-m-d", $ts)));
                        if ($q_1->num_rows() > 0) {
                            foreach ($q_1->result() as $row_1) {
                                $phong[$i] = $row_1;
                            }
                        } else
                            $phong[$i] = 'null';
                    } else {
                        $d = $d - $songay;
                        $t = $thang + 1;
                        $n = $nam;
                        if ($t > 12) {
                            $t = 1;
                            $n = $n + 1;
                        }
                        $ts = mktime(0, 0, 0, $t, $d, $n);
                        $q_1 = $this->db->query($sql, array($row->MaLP, date("Y-m-d", $ts)));
                        if ($q_1->num_rows() > 0) {
                            foreach ($q_1->result() as $row_1) {
                                $phong[$i] = $row_1;
                            }
                        } else
                            $phong[$i] = 'null';
                    }
                }
                $data[$row->MaLP] = $phong;
            }
            $data['khachsan'] = $makhachsan;
            return $data;
        }
    }

    function laygia($makhachsan) {
        $sql = 'select max(SoLuongPhongTrong) as max, GiaSauKhuyenMai as gia from PhongTrongTheoNgay where LoaiPhong = ? and Ngay = ?';
        $this->db->select('MaLP');
        $this->db->from('LoaiPhong');
        $this->db->where('KhachSan', $makhachsan);
        $q = $this->db->get();
        if ($q->num_rows() > 0) {
            foreach ($q->result() as $row) {
                $ngay = $this->input->post('ngay');
                $thang = $this->input->post('thang');
                $nam = $this->input->post('nam');
                $songay = $this->input->post('songay');
                for ($i = 0; $i < 14; $i++) {
                    $d = $ngay + $i;
                    if ($d < $songay + 1) {
                        $ts = mktime(0, 0, 0, $thang, $d, $nam);
                        $q_1 = $this->db->query($sql, array($row->MaLP, date("Y-m-d", $ts)));
                        if ($q_1->num_rows() > 0) {
                            foreach ($q_1->result() as $row_1) {
                                $phong[$i] = $row_1;
                            }
                        } else
                            $phong[$i] = 'null';
                    } else {
                        $d = $d - $songay;
                        $t = $thang + 1;
                        $n = $nam;
                        if ($t > 12) {
                            $t = 1;
                            $n = $n + 1;
                        }
                        $ts = mktime(0, 0, 0, $t, $d, $n);
                        $q_1 = $this->db->query($sql, array($row->MaLP, date("Y-m-d", $ts)));
                        if ($q_1->num_rows() > 0) {
                            foreach ($q_1->result() as $row_1) {
                                $phong[$i] = $row_1;
                            }
                        } else
                            $phong[$i] = 'null';
                    }
                }
                $data[$row->MaLP] = $phong;
            }
            return $data;
        }
    }

    function laygiangay($makhachsan, $ngay, $thang, $nam, $songay) {
        $sql = 'select max(SoLuongPhongTrong) as max, GiaSauKhuyenMai as gia from PhongTrongTheoNgay where LoaiPhong = ? and Ngay = ?';
        $this->db->select('MaLP');
        $this->db->from('LoaiPhong');
        $this->db->where('KhachSan', $makhachsan);
        $q = $this->db->get();
        if ($q->num_rows() > 0) {
            foreach ($q->result() as $row) {
                for ($i = 0; $i < 14; $i++) {
                    $d = $ngay + $i;
                    if ($d < $songay + 1) {
                        $ts = mktime(0, 0, 0, $thang, $d, $nam);
                        $q_1 = $this->db->query($sql, array($row->MaLP, date("Y-m-d", $ts)));
                        if ($q_1->num_rows() > 0) {
                            foreach ($q_1->result() as $row_1) {
                                $phong[$i] = $row_1;
                            }
                        } else
                            $phong[$i] = 'null';
                    } else {
                        $d = $d - $songay;
                        $t = $thang + 1;
                        $n = $nam;
                        if ($t > 12) {
                            $t = 1;
                            $n = $n + 1;
                        }
                        $ts = mktime(0, 0, 0, $t, $d, $n);
                        $q_1 = $this->db->query($sql, array($row->MaLP, date("Y-m-d", $ts)));
                        if ($q_1->num_rows() > 0) {
                            foreach ($q_1->result() as $row_1) {
                                $phong[$i] = $row_1;
                            }
                        } else
                            $phong[$i] = 'null';
                    }
                }
                $data[$row->MaLP] = $phong;
            }
            return $data;
        }
    }

    function laygia_chitiet() {
        $ngay = $this->input->post('ngaybatdau');
        $thang = $this->input->post('thangbatdau');
        $nam = $this->input->post('nambatdau');
        $ts = mktime(0, 0, 0, $thang, $ngay, $nam);
        $startdate = date('Y-m-d', $ts);
        $songay = date('t', $ts);

        $ngayketthuc = $this->input->post('ngayketthuc');
        $thangketthuc = $this->input->post('thangketthuc');
        $namketthuc = $this->input->post('namketthuc');

        $ts = mktime(0, 0, 0, $thangketthuc, $ngayketthuc, $namketthuc);
        $enddate = date('Y-m-d', $ts);

        $loaiphong = $this->input->post('loaiphong');

        $sql = 'select distinct LoaiGiuong, Ten from PhongTrongTheoNgay, LoaiGiuong where LoaiGiuong=MaLG and LoaiPhong = \'' . $loaiphong . '\' and Ngay >= ?  and ngay <= ?';
        $q = $this->db->query($sql, array($startdate, $enddate));
        if ($q->num_rows() > 0) {
            foreach ($q->result() as $row) {
                $loaigiuong[] = $row;
            }
        }

        $tonggia = 0;
        $sophong = 0;
        $sql = 'select SoLuongPhongTrong, GiaSauKhuyenMai, LoaiGiuong from PhongTrongTheoNgay where LoaiPhong = \'' . $loaiphong . '\' and Ngay=?';
        $i = 0;
        $tong = 0;
        do {
            $d = $ngay + $i;
            $max = 0;
            if ($d < $songay + 1) {
                $t = $thang;
                $n = $nam;
                $ts = mktime(0, 0, 0, $t, $d, $n);
                $q_1 = $this->db->query($sql, array(date("Y-m-d", $ts)));
                if ($q_1->num_rows() > 0) {
                    foreach ($q_1->result() as $row_1) {
                        if ($i == 0) {
                            $min[$row_1->LoaiGiuong] = $row_1->SoLuongPhongTrong;
                            $sophong += $row_1->SoLuongPhongTrong;
                        } else if ($min[$row_1->LoaiGiuong] > $row_1->SoLuongPhongTrong) {
                            $sophong -= $min[$row_1->LoaiGiuong];
                            $min[$row_1->LoaiGiuong] = $row_1->SoLuongPhongTrong;
                            $sophong += $row_1->SoLuongPhongTrong;
                        }
                        $tong = $row_1->GiaSauKhuyenMai;
                        $phong[$i] = $row_1;
                    }
                } else
                    $phong[$i] = 'null';
            } else {
                $d = $d - $songay;
                $t = $thang + 1;
                $n = $nam;
                if ($t > 12) {
                    $t = 1;
                    $n = $n + 1;
                }
                $ts = mktime(0, 0, 0, $t, $d, $n);
                $q_1 = $this->db->query($sql, array(date("Y-m-d", $ts)));
                if ($q_1->num_rows() > 0) {
                    foreach ($q_1->result() as $row_1) {
                        if ($i == 0) {
                            $min[$row_1->LoaiGiuong] = $row_1->SoLuongPhongTrong;
                            $sophong += $row_1->SoLuongPhongTrong;
                        } else if ($min[$row_1->LoaiGiuong] > $row_1->SoLuongPhongTrong) {
                            $sophong -= $min[$row_1->LoaiGiuong];
                            $min[$row_1->LoaiGiuong] = $row_1->SoLuongPhongTrong;
                            $sophong += $row_1->SoLuongPhongTrong;
                        }
                        $tong = $row_1->GiaSauKhuyenMai;
                        $phong[$i] = $row_1;
                    }
                } else
                    $phong[$i] = 'null';
            }
            $tonggia = $tonggia + $tong;
            $i++;
        }
        while ($d != $ngayketthuc || $t != $thangketthuc || $n != $namketthuc);
        $data['sodem'] = $i;
        $data['min'] = $min;
        $data['phong'] = $phong;
        $data['tonggia'] = $tonggia;
        $data['loaigiuong'] = $loaigiuong;
        $data['sophong'] = $sophong;
        return $data;
    }

}

?>